package com.yihusitian.zsxq.sg.util;

import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.ds.simple.SimpleDataSource;
import com.google.common.collect.ImmutableMap;
import com.yihusitian.zsxq.sg.entity.FieldInfo;
import org.apache.commons.lang3.StringUtils;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @Description 数据库表元数据
 * @Author LeeHo
 * @Date 2020/8/26 10:35
 */
public class TableMetadataHelper {

	private static Map<String, String> jdbcTypeToJavaTypeMap = new ImmutableMap.Builder<String, String>()
						.put("CHAR", "String")
						.put("VARCHAR", "String")
						.put("LONGVARCHAR", "String")
						.put("NUMERIC", "BigDecimal")
						.put("DECIMAL", "BigDecimal")
						.put("BIT", "Boolean")
						.put("BOOLEAN", "Boolean")
						.put("TINYINT", "Integer")
						.put("SMALLINT", "Integer")
						.put("INT", "Integer")
						.put("BIGINT", "Long")
						.put("FLOAT", "Double")
						.put("DOUBLE", "Double")
						.put("DATE", "Date")
						.put("DATETIME", "Date")
						.put("TIME", "Date")
						.put("TIMESTAMP", "Date")
						.put("TEXT", "String")
						.build();

	private static final String INFORMATION_SCHEMA_DATABASE = "information_schema";

	/**
	 * 获取指定数据库下指定表名的所有字段信息
	 *
	 * @param dataSource
	 * @param databaseName
	 * @param tableName
	 * @return
	 */
	public static List<Entity> getColumnInfoByDataBaseNameAndTableName(DataSource dataSource, String databaseName, String tableName) {
		List<Entity> list = null;
		try {
			list = Db.use(dataSource).findAll(Entity.create("COLUMNS").set("TABLE_SCHEMA", databaseName).set("TABLE_NAME", tableName));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public static String getTableComment(DataSource dataSource, String databaseName, String tableName) {
		String tableComment = null;
		try {
			Entity entity = Db.use(dataSource).get(Entity.create("TABLES").set("TABLE_SCHEMA", databaseName).set("TABLE_NAME", tableName));
			tableComment = entity.getStr("TABLE_COMMENT");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return tableComment;
	}

	/**
	 * 构建DataSource
	 *
	 * @param jdbcUrl
	 * @param user
	 * @param password
	 * @return
	 */
	public static DataSource buildDataSource(String jdbcUrl, String user, String password) {
		if (!jdbcUrl.endsWith("/")) {
			jdbcUrl = jdbcUrl.concat("/");
		}
		return new SimpleDataSource(jdbcUrl.concat(INFORMATION_SCHEMA_DATABASE), user, password);
	}

	/**
	 * 查找指定数据库中指定表中的所有字段信息
	 *
	 * @param dataSource
	 * @param databaseName
	 * @param tableName
	 * @return
	 */
	public static List<FieldInfo> getFieldInfoByDataBaseNameAndTableName(DataSource dataSource, String databaseName, String tableName) {
		List<Entity> entityList = getColumnInfoByDataBaseNameAndTableName(dataSource, databaseName, tableName);
		if (entityList == null) {
			return null;
		}
		List<FieldInfo> result = new ArrayList<FieldInfo>(entityList.size());
		entityList.forEach(entity -> {
			// COLUMN_NAME, DATA_TYPE, COLUMN_KEY, COLUMN_COMMENT
			String columnField = entity.getStr("COLUMN_NAME");
			// 下划线转驼峰
			String propertyName = StrUtil.toCamelCase(columnField);
			String javaType = jdbcTypeToJavaType(entity.getStr("DATA_TYPE").toUpperCase());
			String comment = entity.getStr("COLUMN_COMMENT");
			// 是否为主键ID
			boolean isPrimaryId = "PRI".equals(entity.getStr("COLUMN_KEY"));
			boolean isPrimaryIdAutoIncrement = false;
			if  (isPrimaryId) {
				String extra = entity.getStr("EXTRA");
				isPrimaryIdAutoIncrement = "auto_increment".equals(StringUtils.defaultString(extra));
			}
			result.add(FieldInfo.builder().columnField(columnField).propertyName(propertyName).javaType(javaType).comment(comment).isPrimaryId(isPrimaryId).isPrimaryIdAutoIncrement(isPrimaryIdAutoIncrement).build());
		});
		return result;
	}

	/**
	 * JDBC类型转JAVA类型
	 *
	 * @param jdbcType
	 * @return
	 */
	public static String jdbcTypeToJavaType(String jdbcType) {
		jdbcType = jdbcType.toUpperCase();
		if (jdbcTypeToJavaTypeMap.containsKey(jdbcType)) {
			return jdbcTypeToJavaTypeMap.get(jdbcType);
		}
		throw new RuntimeException(String.format("JdbcType %s has no match to JavaType!", jdbcType));
	}
}
